---
layout: default
---
<div class="wikidoc">
<p><span style="text-decoration:underline"><strong>Project Description</strong></span></p>
<p>This project contains a set of SQLCLR User-defined Aggregate functions (SQLCLR UDAs) that collectively offer similar functionality to the MySQL GROUP_CONCAT function. There are multiple functions to ensure the best performance based on the functionality
 required.</p>
<p>The SQLCLR objects in this project allow you to write code like this:</p>
<blockquote>
<p><code>SELECT some_id,<br>
&nbsp; &nbsp; &nbsp; &nbsp;dbo.GROUP_CONCAT(some_column)&nbsp;AS&nbsp;as delimited_list<br>
FROM dbo.some_table<br>
GROUP BY&nbsp;some_id;</code></p>
</blockquote>
<p>&nbsp; &nbsp; to retrieve a resultset like this:</p>
<blockquote>
<p><code>some_id &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; delimited_list<br>
----------------- -----------------------------------<br>
1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; red,green,blue<br>
2 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cyan,magenta,yellow,key</code></p>
</blockquote>
<p>MySQL offers an aggregate function that will concatenate grouped strings into a delimited list. There are many ways to accomplish the same result on SQL Server using various T-SQL methods, the most efficient of those native methods seems to be using XML
 functionality built into T-SQL, however none of the native&nbsp;T-SQL solutions offer as expressive a syntax as these SQLCLR UDAs.&nbsp;An extensive set of performance tests were carried out and the SQLCLR UDAs in this project are comparable in terms of performance
 to the T-SQL <em>XML PATH</em> and <em>XML PATH, TYPE</em>&nbsp;methods. In some scenarios the SQLCLR&nbsp;UDAs performed best (least CPU and elapsed time) and in other scenarios the T-SQL XML methods performed best. Overall performance is comparable between
 the SQLCLR&nbsp;UDAs and T-SQL XML methods. You can read more about the different options and their performance compared to these objects
<a href="http://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation" target="_blank">
here</a>.<br>
<br>
In Microsoft SQL Server 2008 R2&nbsp;Books Online the example presented for a SQLCLR&nbsp;UDA is an aggregate named Concatenate that offers similar functionality to MySQL's built-in GROUP_CONCAT function. The article resides
<a title="here" href="http://msdn.microsoft.com/en-us/library/ms131056(v=sql.105).aspx" target="_blank">
here</a>.&nbsp;The example aggregate function uses a StringBuilder to store the data internally. While the implementation is a proper example of a SQLCLR UDA it does not perform well over data sets that produce very long concatenations. The SQLCLR&nbsp;UDAs
 in this project implement a more efficient internal data structure than the StringBuilder used in the MSDN example.</p>
<p>This project contains the following aggregates:</p>
<ul>
<li><strong>GROUP_CONCAT</strong> </li><li><strong>GROUP_CONCAT_D</strong> </li><li><strong>GROUP_CONCAT_DS</strong> </li><li><strong>GROUP_CONCAT_S</strong> </li></ul>
<p>Visit the <a href="documentation.html">Documentation Page</a> for a detailed description of each aggregate.&nbsp;</p>
<p><strong><span style="text-decoration:underline">Installation and Usage</span></strong></p>
<p><strong><span style="font-weight:normal">To install the SQLCLR&nbsp;UDAs you will need only to execute one T-SQL script contained within the release downloadable attainable on the
<a href="http://groupconcat.codeplex.com/releases/">Downloads Page</a>. Note that Visual Studio is
<em>not</em> required to install or use the SQLCLR UDAs. See the <a href="documentation.html">
Documentation Page</a> for additional deployment and usage details.</span></strong></p>
<p><strong><span style="font-weight:normal">Note that use of the </span></strong><span style="font-weight:normal">SQLCLR&nbsp;UDAs</span><strong><span style="font-weight:normal">&nbsp;implies you will enable the SQLCLR on your database instance.
</span></strong></p>
<p><strong><span style="font-weight:normal">For 32-bit instances where these functions will be used extensively I recommend you monitor memory usage closely and adjust your MemToLeave settings appropriately.<br>
</span></strong></p>
<p><span style="text-decoration:underline"><strong>Support</strong></span></p>
<p>If you would like to suggest a new feature or report a bug please add a new item using the
<a href="https://github.com/orlando-colamatteo/ms-sql-server-group-concat-sqlclr/issues">Issue Tracker</a>.</p>
</div><div class="ClearBoth"></div>
